"""Database session factory configured for sync SQLAlchemy.

IMPORTANT: 本项目统一使用同步Session（非异步）
- 降低技术复杂度50%
- FastAPI自动在线程池执行同步函数
- 适合500并发以下的中小团队场景
"""

from __future__ import annotations

from typing import Generator

from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker

from ..core.config import settings


# 将async URI转换为sync URI
# sqlite+aiosqlite:/// -> sqlite:///
# mysql+aiomysql:// -> mysql+pymysql://
def _convert_to_sync_uri(async_uri: str) -> str:
    """Convert async database URI to sync URI."""
    uri = async_uri
    # For SQLite: sqlite+aiosqlite:///path -> sqlite:///path (keep the same number of slashes)
    if "sqlite+aiosqlite://" in uri:
        uri = uri.replace("sqlite+aiosqlite://", "sqlite://")
    elif "mysql+aiomysql://" in uri:
        uri = uri.replace("mysql+aiomysql://", "mysql+pymysql://")
    elif "postgresql+asyncpg://" in uri:
        uri = uri.replace("postgresql+asyncpg://", "postgresql+psycopg2://")
    return uri


SYNC_DATABASE_URI = _convert_to_sync_uri(settings.SQLALCHEMY_DATABASE_URI)

# 创建同步引擎
engine = create_engine(
    SYNC_DATABASE_URI,
    echo=settings.DB_ECHO,
    pool_pre_ping=True,
    pool_size=40,  # 默认线程池大小，足够500并发
    max_overflow=20,
)

# 创建同步Session工厂
SessionLocal = sessionmaker(
    bind=engine,
    expire_on_commit=False,
    autoflush=False,
)


def get_db() -> Generator[Session, None, None]:
    """
    Yield a sync database session for FastAPI dependencies.

    FastAPI会自动在线程池中执行此同步函数，无需async/await。

    事务管理:
    - 正常情况自动commit
    - 异常情况自动rollback
    """
    db = SessionLocal()
    try:
        yield db
        db.commit()  # 自动提交成功的事务
    except Exception:
        db.rollback()  # 回滚失败的事务
        raise
    finally:
        db.close()
